import os
import sqlite3
from sqlite3 import Connection

from nonebot import get_driver, logger

from ..utils.singleton import SingletonMeta

DB_NAME: str = 'teach.db'


class DBConnection(metaclass=SingletonMeta):
    def __init__(self):
        self.connection: Connection = None


def connectDB():
    db_connection = DBConnection()
    if db_connection.connection is not None:
        return
    if not os.path.exists(DB_NAME):
        createTable()
    db_connection.connection = sqlite3.connect(DB_NAME)


def createTable():
    con = sqlite3.connect(DB_NAME)
    cur = con.cursor()
    cur.executescript('''
-- "User" definition

CREATE TABLE "User" (
	qqid INTEGER NOT NULL,
	studentId INTEGER,
	name TEXT(10),
	CONSTRAINT User_PK PRIMARY KEY (qqid)
);

-- ClassStudent definition

CREATE TABLE ClassStudent (
	gid INTEGER NOT NULL,
	studentid INTEGER NOT NULL
);
    
-- "Assignment" definition

CREATE TABLE "Assignment" (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	gid INTEGER,
	name TEXT,
	endTimestamp INTEGER NOT NULL,
	"type" INTEGER NOT NULL
);

-- SubmitRecord definition

CREATE TABLE SubmitRecord (
	assignmentId INTEGER NOT NULL,
	studentId INTEGER NOT NULL,
	uploaded INTEGER NOT NULL
);

CREATE INDEX SubmitRecord_assignmentId_IDX ON SubmitRecord (assignmentId);
    ''')
    cur.close()
    con.commit()

    con.close()


driver = get_driver()


@driver.on_bot_connect
async def start(bot):
    if DBConnection().connection is None:
        connectDB()
        logger.opt(colors=True).info("<y>Connect database successfully</y>")


@driver.on_bot_disconnect
async def teardown(bot):
    if DBConnection().connection is not None:
        DBConnection().connection.close()
        logger.debug("Connection closed")
